package com.sunzm.common.utils.mysql

import java.sql.{DriverManager, PreparedStatement}

import org.apache.commons.lang3.StringUtils

/**
 *
 * SQL语句生成工具类
 *
 * @author Administrator
 * @version 1.0
 * @date 2021-06-22 19:30
 */
object SQLBuildUtils {
  /**
   * 构建预编译的SQL语句
   *
   * @param tableName            表名
   * @param primaryKeyArray      主键的字段数组
   * @param updateFieldArray     需要更新的字段数组
   * @param accumulateFieldArray 需要覆盖的字段数组
   * @param maxFieldArray        需要取最大值的字段数组
   * @param minFieldArray        需要取最小值的字段数组
   * @return
   */
  def buidPrepareSQL(tableName: String, primaryKeyArray: Array[String], updateFieldArray: Array[String],
                     accumulateFieldArray: Array[String], maxFieldArray: Array[String] = Array.empty[String],
                     minFieldArray: Array[String] = Array.empty[String]) = {
    val sqlBuilder = new StringBuilder(s"INSERT INTO ${tableName}(")
    var fieldCount = 0
    primaryKeyArray.foreach(field => {
      sqlBuilder.append(s"${field}, ")
      fieldCount += 1
    })
    updateFieldArray.foreach(field => {
      sqlBuilder.append(s"${field}, ")
      fieldCount += 1
    })
    accumulateFieldArray.foreach(field => {
      sqlBuilder.append(s"${field}, ")
      fieldCount += 1
    })
    maxFieldArray.foreach(field => {
      sqlBuilder.append(s"${field}, ")
      fieldCount += 1
    })
    minFieldArray.foreach(field => {
      sqlBuilder.append(s"${field}, ")
      fieldCount += 1
    })

    //删除最后一个逗号
    sqlBuilder.deleteCharAt(sqlBuilder.lastIndexOf(","))

    sqlBuilder.append(") VALUES(")

    //追加?
    for (i <- 0 until fieldCount) {
      if (i == (fieldCount - 1)) {
        sqlBuilder.append("?)")
      } else {
        sqlBuilder.append("?, ")
      }
    }

    sqlBuilder.append(" ON DUPLICATE KEY UPDATE ")

    updateFieldArray.foreach(field => {
      sqlBuilder.append(s"${field} = ?, ")
    })
    accumulateFieldArray.foreach(field => {
      sqlBuilder.append(s"${field} = ${field} + ?, ")
    })
    maxFieldArray.foreach(field => {
      sqlBuilder.append(s"${field} = MAX(${field}, ?), ")
    })
    minFieldArray.foreach(field => {
      sqlBuilder.append(s"${field} = MIN(${field}, ?), ")
    })

    //删除最后一个逗号
    sqlBuilder.deleteCharAt(sqlBuilder.lastIndexOf(","))

    sqlBuilder.toString
  }

  /**
   * 构建完整的SQL语句（带具体的值的）
   *
   * @param tableName
   * @param primaryMap
   * @param updateFieldMap
   * @param accumulateFieldMap
   * @param maxFieldMap
   * @param minFieldMap
   * @return
   */
  def buidMapSQL(tableName: String, primaryMap: Map[String, String],
                 updateFieldMap: Map[String, String],
                 accumulateFieldMap: Map[String, String],
                 maxFieldMap: Map[String, String] = Map.empty[String, String],
                 minFieldMap: Map[String, String] = Map.empty[String, String]) = {

    val sqlBuilder = new StringBuilder(s"INSERT INTO ${tableName}(")
    val insertBuilder = new StringBuilder(s" VALUES(")

    val allMap = primaryMap ++ updateFieldMap ++ maxFieldMap ++ minFieldMap
    allMap.foreach(fieldMap => {
      val fieldKey = fieldMap._1
      val fieldValue = StringUtils.trim(fieldMap._2)
      sqlBuilder.append(s"${fieldKey}, ")
      insertBuilder.append(s"'${fieldValue}', ")
    })

    //删除最后一个逗号
    sqlBuilder.deleteCharAt(sqlBuilder.lastIndexOf(","))
    insertBuilder.deleteCharAt(insertBuilder.lastIndexOf(",")).append(") ")

    sqlBuilder.append(")")

    sqlBuilder.append(insertBuilder.toString)

    sqlBuilder.append(" ON DUPLICATE KEY UPDATE ")

    updateFieldMap.foreach(fieldMap => {
      val fieldKey = fieldMap._1
      val fieldValue = fieldMap._2
      sqlBuilder.append(s"${fieldKey} = '${fieldValue}', ")
    })

    accumulateFieldMap.foreach(fieldMap => {
      val fieldKey = fieldMap._1
      val fieldValue = fieldMap._2
      sqlBuilder.append(s"${fieldKey} = ${fieldKey} + '${fieldValue}', ")
    })

    maxFieldMap.foreach(fieldMap => {
      val fieldKey = fieldMap._1
      val fieldValue = fieldMap._2
      sqlBuilder.append(s"${fieldKey} = MAX(${fieldKey}, '${fieldValue}'), ")
    })

    minFieldMap.foreach(fieldMap => {
      val fieldKey = fieldMap._1
      val fieldValue = fieldMap._2
      sqlBuilder.append(s"${fieldKey} = MIN(${fieldKey}, '${fieldValue}'), ")
    })

    //删除最后一个逗号
    sqlBuilder.deleteCharAt(sqlBuilder.lastIndexOf(","))

    sqlBuilder.toString
  }

  /**
   * 给 PreparedStatement 中的占位符赋值
   *
   * @param ps
   * @param primaryValueArray
   * @param fieldValueArray
   */
  def setStatement(ps: PreparedStatement,
                   primaryValueArray: Array[String],
                   fieldValueArray: Array[String]) = {
    //fieldValueArray会使用2次
    val newFieldValueArray = fieldValueArray ++ fieldValueArray
    val arraySize = primaryValueArray.size + newFieldValueArray.size
    for (i <- 1 to arraySize) {
      if (i < primaryValueArray.size + 1) {
        ps.setString(i, primaryValueArray(i - 1))
      } else {
        ps.setString(i, newFieldValueArray(i - primaryValueArray.size - 1))
      }
    }
  }

  def main(args: Array[String]): Unit = {
    val tableName = "rpt_icall_cdr_task_report"

    //主键对应的Key-Value
    val primaryKeyMap: Map[String, String] = Map[String, String]("company_id" -> "cmp001",
      "task_id" -> "tsk002",
      "result_date" -> "2021-02-14")
    //需要更新的字段对应的Key-Value
    val updateFieldMap: Map[String, String] = Map[String, String](("task_name" -> "任务001"))
    //需要累加的字段对应的Key-Value
    val accumulateFieldMap: Map[String, String] = Map[String, String]("dailed_custom_count" -> "1",
      "dailed_count" -> "0")
    //需要求最大值的字段对应的Key-Value
    val maxFieldMap: Map[String, String] = Map[String, String]("max_duration" -> "31000",
      "max_count" -> "200")
    //需要求最小值的字段对应的Key-Value
    val minFieldMap: Map[String, String] = Map[String, String]("min_duration" -> "15000",
      "min_count" -> "10")

    //生成预编译语句测试
    val primaryKeyArray: Array[String] = primaryKeyMap.keys.toArray
    val updateFieldArray = updateFieldMap.keys.toArray
    val accumulateFieldArray = accumulateFieldMap.keys.toArray
    val maxFieldArray = maxFieldMap.keys.toArray
    val minFieldArray = minFieldMap.keys.toArray

    val prepareSQL = buidPrepareSQL(tableName,
      primaryKeyArray,
      updateFieldArray,
      accumulateFieldArray,
      maxFieldArray,
      minFieldArray
    )

    println(s"prepareSQL: ${prepareSQL}")

    val prepareWithoutMaxAndMinSQL = buidPrepareSQL(tableName,
      primaryKeyArray,
      updateFieldArray,
      accumulateFieldArray
    )

    println(s"prepareWithoutMaxAndMinSQL: ${prepareWithoutMaxAndMinSQL}")


    val mapSQL = buidMapSQL(tableName, primaryKeyMap, updateFieldMap, accumulateFieldMap, maxFieldMap, minFieldMap)

    println(s"buidMapSQL: ${mapSQL}")

    val conn = DriverManager.getConnection("jdbc:mysql://192.168.1.208:3311/sobot_db",
      "sobot_icall", "SoboT321")

    val ps = conn.prepareStatement(prepareWithoutMaxAndMinSQL)

    val primaryValueArray: Array[String] = primaryKeyMap.values.toArray
    val updateFieldValueArray = updateFieldMap.values.toArray
    val accumulateFieldValueArray = accumulateFieldMap.values.toArray
    val maxFieldValueArray = maxFieldMap.values.toArray
    val minFieldValueArray = minFieldMap.values.toArray

    val allFiledValueArray = updateFieldValueArray ++ accumulateFieldValueArray ++ maxFieldValueArray ++ minFieldValueArray
    setStatement(ps, primaryValueArray, allFiledValueArray)

    ps.execute()

    ps.close()
    conn.close()

  }
}
